package br.ufpb.sistcomp.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Class responsible for managing the operations of the database. ps: The table
 * creation script is only executed the first time that this class is stagnant,
 * if all the tables are cleared, the method should be executed runScript()
 * passing as argument the script to create all the tables.
 * 
 * @author Diego Sousa, www.diegosousa.com
 * @author Renan Oliveira, www.renanoliveira.com
 * @version 2.0
 * @since 25/01/2012
 * 
 *        Copyright (C) 2012 SistComp Empresa Jr.
 */

public class ManagerDatabase extends SQLiteOpenHelper {

	private Context context;

	/*
	 * The "super" creates the database and the getWritableDatabase() give
	 * written authorization in the database.
	 */

	public ManagerDatabase(Context context) {
		super(context, InformationDatabase.NAME_DATABASE, null,
				InformationDatabase.VERSION_DATABASE);
		this.context = context;
		getWritableDatabase();
	}

	/*
	 * The method onCreate is called only the first time that this class is
	 * stagnant.
	 */

	@Override
	public void onCreate(SQLiteDatabase sqLiteDatabase) {
		Log.i("FLAG", "Creating the database...");

		if (sqLiteDatabase != null) {
			Log.i("Flag", "Activating the option of foreign key...");
			sqLiteDatabase.execSQL("PRAGMA foreign_keys=ON;");
		}

		runScript(sqLiteDatabase, InformationDatabase.SCRIPT_CREATE_ALL_TABLE);

	}

	/*
	 * Whenever this method is called the database version is changed. Its
	 * function is to create a backup of the database tables and delete all of
	 * the current database.
	 */

	@Override
	public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion,
			int newVersion) {	
		
		ContentValues contentValues = new ContentValues();
		
		SQLiteDatabase sqLiteDatabase_backup = this.context
				.openOrCreateDatabase(InformationDatabase.NAME_BACKUP_DATABASE,
						Context.MODE_PRIVATE, null);

		runScript(sqLiteDatabase_backup,
				InformationDatabase.SCRIPT_CREATE_ALL_TABLE);

		backupTableTeachers(sqLiteDatabase, sqLiteDatabase_backup, contentValues);

		backupTableSemesters(sqLiteDatabase, sqLiteDatabase_backup, contentValues);

		backupTableDisciplines(sqLiteDatabase, sqLiteDatabase_backup, contentValues);

		backupTablePoints(sqLiteDatabase, sqLiteDatabase_backup, contentValues);

		runScript(sqLiteDatabase, InformationDatabase.SCRIPT_DELETE_ALL_TABLE);
		

	}

	private void backupTableTeachers(SQLiteDatabase sqLiteCurrentBase,
			SQLiteDatabase sqLiteBackupBase, ContentValues cvTeacher) {
						
		Cursor cursorTeacher = sqLiteCurrentBase.query(
				InformationDatabase.GETNAME_TABLE_TEACHERS, new String[] {
						InformationDatabase.ATRIBUTE_TEACHERS_ID_TEACHER,
						InformationDatabase.ATRIBUTE_TEACHERS_NAME,
						InformationDatabase.ATRIBUTE_TEACHERS_MAIL,
						InformationDatabase.ATRIBUTE_TEACHERS_PHONE,
						InformationDatabase.ATRIBUTE_TEACHERS_CELL }, null,
				null, null, null, null);

		if (cursorTeacher != null) {
			while (!cursorTeacher.isAfterLast()) {

				if (cursorTeacher.getPosition() == -1) {
					cursorTeacher.moveToFirst();
				}

				int columnId_teachers = cursorTeacher
						.getInt(cursorTeacher
								.getColumnIndex(InformationDatabase.ATRIBUTE_TEACHERS_ID_TEACHER));
				String columnName = cursorTeacher
						.getString(cursorTeacher
								.getColumnIndex(InformationDatabase.ATRIBUTE_TEACHERS_NAME));
				String columnMail = cursorTeacher
						.getString(cursorTeacher
								.getColumnIndex(InformationDatabase.ATRIBUTE_TEACHERS_MAIL));
				String columnPhone = cursorTeacher
						.getString(cursorTeacher
								.getColumnIndex(InformationDatabase.ATRIBUTE_TEACHERS_PHONE));
				String columnCell = cursorTeacher
						.getString(cursorTeacher
								.getColumnIndex(InformationDatabase.ATRIBUTE_TEACHERS_CELL));

				cvTeacher.put(
						InformationDatabase.ATRIBUTE_TEACHERS_ID_TEACHER,
						columnId_teachers);
				cvTeacher.put(InformationDatabase.ATRIBUTE_TEACHERS_NAME,
						columnName);
				cvTeacher.put(InformationDatabase.ATRIBUTE_TEACHERS_MAIL,
						columnMail);
				cvTeacher.put(InformationDatabase.ATRIBUTE_TEACHERS_PHONE,
						columnPhone);
				cvTeacher.put(InformationDatabase.ATRIBUTE_TEACHERS_CELL,
						columnCell);

				sqLiteBackupBase.insert(
						InformationDatabase.GETNAME_TABLE_TEACHERS, null,
						cvTeacher);

				cursorTeacher.moveToNext();
			}
			cvTeacher.clear();
		}
	}

	private void backupTableSemesters(SQLiteDatabase sqLiteCurrentBase,
			SQLiteDatabase sqLiteBackupBase, ContentValues cvSemester) {
						
		Cursor cursorSemester = sqLiteCurrentBase.query(
				InformationDatabase.GETNAME_TABLE_SEMESTERS, new String[] {
						InformationDatabase.ATRIBUTE_SEMESTERS_ID_SEMESTERS,
						InformationDatabase.ATRIBUTE_SEMESTERS_NAME }, null,
				null, null, null, null);

		if (cursorSemester != null) {
			while (!cursorSemester.isAfterLast()) {

				if (cursorSemester.getPosition() == -1) {
					cursorSemester.moveToFirst();
				}

				
				int columnId_semesters = cursorSemester
						.getInt(cursorSemester
								.getColumnIndex(InformationDatabase.ATRIBUTE_SEMESTERS_ID_SEMESTERS));
				String columnName = cursorSemester
						.getString(cursorSemester
								.getColumnIndex(InformationDatabase.ATRIBUTE_SEMESTERS_NAME));
				
				cvSemester.put(
						InformationDatabase.ATRIBUTE_SEMESTERS_ID_SEMESTERS,
						columnId_semesters);
				cvSemester.put(InformationDatabase.ATRIBUTE_SEMESTERS_NAME,
						columnName);								

				sqLiteBackupBase.insert(
						InformationDatabase.GETNAME_TABLE_SEMESTERS, null,
						cvSemester);

				cursorSemester.moveToNext();
			}
			cvSemester.clear();
		}
	}

	private void backupTableDisciplines(SQLiteDatabase sqLiteCurrentBase,
			SQLiteDatabase sqLiteBackupBase, ContentValues cvDiscipline) {				

		Cursor cursorDiscipline = sqLiteCurrentBase
				.query(InformationDatabase.GETNAME_TABLE_DISCIPLINES,
						new String[] {
								InformationDatabase.ATRIBUTE_DISCIPLINES_ID_DISCIPLINES,
								InformationDatabase.ATRIBUTE_DISCIPLINES_ID_TEACHER,
								InformationDatabase.ATRIBUTE_DISCIPLINES_ID_SEMESTER,
								InformationDatabase.ATRIBUTE_DISCIPLINES_NAME,								
								InformationDatabase.ATRIBUTE_DISCIPLINES_HOURS,
								InformationDatabase.ATRIBUTE_DISCIPLINES_MEDIA },
						null, null, null, null, null);

		if (cursorDiscipline != null) {
			while (!cursorDiscipline.isAfterLast()) {

				if (cursorDiscipline.getPosition() == -1) {
					cursorDiscipline.moveToFirst();
				}

				int columnId_discipline = cursorDiscipline
						.getInt(cursorDiscipline
								.getColumnIndex(InformationDatabase.ATRIBUTE_DISCIPLINES_ID_DISCIPLINES));
				int columnId_teacher = cursorDiscipline
						.getInt(cursorDiscipline
								.getColumnIndex(InformationDatabase.ATRIBUTE_DISCIPLINES_ID_TEACHER));
				int columnId_semester = cursorDiscipline
						.getInt(cursorDiscipline
								.getColumnIndex(InformationDatabase.ATRIBUTE_DISCIPLINES_ID_SEMESTER));
				String columnName = cursorDiscipline
						.getString(cursorDiscipline
								.getColumnIndex(InformationDatabase.ATRIBUTE_DISCIPLINES_NAME));
				double columnMedia = cursorDiscipline
						.getDouble(cursorDiscipline
								.getColumnIndex(InformationDatabase.ATRIBUTE_DISCIPLINES_MEDIA));
				String columnHours = cursorDiscipline
						.getString(cursorDiscipline
								.getColumnIndex(InformationDatabase.ATRIBUTE_DISCIPLINES_HOURS));

				cvDiscipline
						.put(InformationDatabase.ATRIBUTE_DISCIPLINES_ID_DISCIPLINES,
								columnId_discipline);
				cvDiscipline.put(
						InformationDatabase.ATRIBUTE_DISCIPLINES_ID_TEACHER,
						columnId_teacher);
				cvDiscipline.put(
						InformationDatabase.ATRIBUTE_DISCIPLINES_ID_SEMESTER,
						columnId_semester);
				cvDiscipline.put(
						InformationDatabase.ATRIBUTE_DISCIPLINES_NAME,
						columnName);
				cvDiscipline.put(
						InformationDatabase.ATRIBUTE_DISCIPLINES_MEDIA,
						columnMedia);
				cvDiscipline.put(
						InformationDatabase.ATRIBUTE_DISCIPLINES_HOURS,
						columnHours);

				sqLiteBackupBase.insert(
						InformationDatabase.GETNAME_TABLE_DISCIPLINES, null,
						cvDiscipline);

				cursorDiscipline.moveToNext();

			}
			cvDiscipline.clear();
		}
	}

	private void backupTablePoints(SQLiteDatabase sqLiteCurrentBase,
			SQLiteDatabase sqLiteBackupBase, ContentValues cvPoint) {				

		Cursor cursorPoint = sqLiteCurrentBase.query(				
						InformationDatabase.GETNAME_TABLE_POINTS, new String[] {
								InformationDatabase.ATRIBUTE_POINTS_ID_POINT,
								InformationDatabase.ATRIBUTE_POINTS_ID_DISCIPLINE,
								InformationDatabase.ATRIBUTE_POINTS_VALUES_POINT },
						null, null, null, null, null);

		if (cursorPoint != null) {
			while (!cursorPoint.isAfterLast()) {

				if (cursorPoint.getPosition() == -1) {
					cursorPoint.moveToFirst();
				}

				int columnId_point = cursorPoint
						.getInt(cursorPoint
								.getColumnIndex(InformationDatabase.ATRIBUTE_POINTS_ID_POINT));
				int columnId_discipline = cursorPoint
						.getInt(cursorPoint
								.getColumnIndex(InformationDatabase.ATRIBUTE_POINTS_ID_DISCIPLINE));
				double columnValues_point = cursorPoint.getDouble(cursorPoint
						.getColumnIndex(InformationDatabase.ATRIBUTE_POINTS_VALUES_POINT));

				cvPoint.put(InformationDatabase.ATRIBUTE_POINTS_ID_POINT, columnId_point);
				cvPoint.put(InformationDatabase.ATRIBUTE_POINTS_ID_DISCIPLINE, columnId_discipline);
				cvPoint.put(InformationDatabase.ATRIBUTE_POINTS_VALUES_POINT, columnValues_point);

				sqLiteBackupBase.insert(
						InformationDatabase.GETNAME_TABLE_POINTS, null,
						cvPoint);

				cursorPoint.moveToNext();
			}
			cvPoint.clear();
		}
	}

	/*
	 * Run any script except for the select and/or any other that requires a
	 * return.
	 */
	// @Override
	// public void onDowngrade(SQLiteDatabase db, int oldVersion, int
	// newVersion){
	//
	//
	// }

	public synchronized void runScript(SQLiteDatabase sqLiteDatabase,
			String[] script) {

		for (String sql : script) {
			sqLiteDatabase.execSQL(sql);
		}
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);

	}

	/*
	 * closed instance SQLiteOpenHelper.
	 */
	@Override
	public void close() {
		super.close();		
	}
}